package com.lqf.compare.mysql;

import com.lqf.compare.mysql.model.ColumnInfo;
import com.lqf.compare.mysql.model.IndexInfo;
import com.lqf.compare.mysql.model.TableInfo;

import java.util.*;

public class DatabaseCompareUtil {
    
    /**
     * 比较表注释
     */
    public static void compareTableComments(TableInfo info1, TableInfo info2, List<String> differences) {
        if (!Objects.equals(info1.getTableComment(), info2.getTableComment())) {
            differences.add(String.format("表注释不同: '%s' vs '%s'",
                    info1.getTableComment(), info2.getTableComment()));
        }
    }

    /**
     * 比较列信息
     */
    public static void compareColumns(TableInfo info1, TableInfo info2, List<String> differences) {
        Map<String, ColumnInfo> cols1 = new HashMap<>();
        Map<String, ColumnInfo> cols2 = new HashMap<>();

        for (ColumnInfo col : info1.getColumns()) {
            cols1.put(col.getColumnName(), col);
        }
        for (ColumnInfo col : info2.getColumns()) {
            cols2.put(col.getColumnName(), col);
        }

        Set<String> allColumns = new HashSet<>();
        allColumns.addAll(cols1.keySet());
        allColumns.addAll(cols2.keySet());

        for (String colName : allColumns) {
            ColumnInfo c1 = cols1.get(colName);
            ColumnInfo c2 = cols2.get(colName);

            if (c1 == null) {
                differences.add("列缺失: 第一个数据库中缺少列 '" + colName + "'");
            } else if (c2 == null) {
                differences.add("列缺失: 第二个数据库中缺少列 '" + colName + "'");
            } else {
                // 比较列的属性
                if (!c1.getDataType().equalsIgnoreCase(c2.getDataType())) {
                    differences.add(String.format("列 '%s' 数据类型不同: '%s' vs '%s'",
                            colName, c1.getDataType(), c2.getDataType()));
                }

                if (!equalsIgnoreNull(c1.getColumnComment(), c2.getColumnComment())) {
                    differences.add(String.format("列 '%s' 注释不同: '%s' vs '%s'",
                            colName, c1.getColumnComment(), c2.getColumnComment()));
                }

                if (!equalsIgnoreNull(c1.getIsNullable(), c2.getIsNullable())) {
                    differences.add(String.format("列 '%s' 可空性不同: '%s' vs '%s'",
                            colName, c1.getIsNullable(), c2.getIsNullable()));
                }

                if (!equalsIgnoreNull(c1.getColumnKey(), c2.getColumnKey())) {
                    differences.add(String.format("列 '%s' 键信息不同: '%s' vs '%s'",
                            colName, c1.getColumnKey(), c2.getColumnKey()));
                }

                if (!equalsIgnoreNull(c1.getExtra(), c2.getExtra())) {
                    differences.add(String.format("列 '%s' 额外信息不同: '%s' vs '%s'",
                            colName, c1.getExtra(), c2.getExtra()));
                }

                // 比较默认值
                if (!equalsIgnoreNull(c1.getDefaultValue(), c2.getDefaultValue())) {
                    differences.add(String.format("列 '%s' 默认值不同: '%s' vs '%s'",
                            colName, getValueForDisplay(c1.getDefaultValue()), getValueForDisplay(c2.getDefaultValue())));
                }
            }
        }
    }
    
    /**
     * 比较索引
     */
    public static void compareIndexes(TableInfo info1, TableInfo info2, List<String> differences) {
        Map<String, IndexInfo> indexes1 = new HashMap<>();
        Map<String, IndexInfo> indexes2 = new HashMap<>();
        
        if (info1.getIndexes() != null) {
            for (IndexInfo index : info1.getIndexes()) {
                indexes1.put(index.getIndexName(), index);
            }
        }
        
        if (info2.getIndexes() != null) {
            for (IndexInfo index : info2.getIndexes()) {
                indexes2.put(index.getIndexName(), index);
            }
        }

        Set<String> allIndexes = new HashSet<>();
        allIndexes.addAll(indexes1.keySet());
        allIndexes.addAll(indexes2.keySet());

        for (String indexName : allIndexes) {
            IndexInfo i1 = indexes1.get(indexName);
            IndexInfo i2 = indexes2.get(indexName);

            if (i1 == null) {
                differences.add("索引缺失: 第一个数据库中缺少索引 '" + indexName + "'");
            } else if (i2 == null) {
                differences.add("索引缺失: 第二个数据库中缺少索引 '" + indexName + "'");
            } else {
                // 比较索引的属性
                if (!Objects.equals(i1.getIndexType(), i2.getIndexType())) {
                    differences.add(String.format("索引 '%s' 类型不同: '%s' vs '%s'",
                            indexName, i1.getIndexType(), i2.getIndexType()));
                }
                
                if (!Objects.equals(i1.getIndexMethod(), i2.getIndexMethod())) {
                    differences.add(String.format("索引 '%s' 方法不同: '%s' vs '%s'",
                            indexName, i1.getIndexMethod(), i2.getIndexMethod()));
                }

                if (!Objects.equals(i1.getColumnNames(), i2.getColumnNames())) {
                    differences.add(String.format("索引 '%s' 列名不同: '%s' vs '%s'",
                            indexName, i1.getColumnNames(), i2.getColumnNames()));
                }

                if (!Objects.equals(i1.getColumnOrders(), i2.getColumnOrders())) {
                    differences.add(String.format("索引 '%s' 列排序不同: '%s' vs '%s'",
                            indexName, i1.getColumnOrders(), i2.getColumnOrders()));
                }
                
                if (i1.isNonUnique() != i2.isNonUnique()) {
                    differences.add(String.format("索引 '%s' 唯一性不同: '%s' vs '%s'",
                            indexName, i1.isNonUnique() ? "非唯一" : "唯一", i2.isNonUnique() ? "非唯一" : "唯一"));
                }
            }
        }
    }
    
    /**
     * 标记差异的列
     */
    public static void markColumnDifferences(TableInfo info1, TableInfo info2) {
        Map<String, ColumnInfo> cols1 = new HashMap<>();
        Map<String, ColumnInfo> cols2 = new HashMap<>();

        for (ColumnInfo col : info1.getColumns()) {
            cols1.put(col.getColumnName(), col);
        }
        for (ColumnInfo col : info2.getColumns()) {
            cols2.put(col.getColumnName(), col);
        }

        Set<String> allColumns = new HashSet<>();
        allColumns.addAll(cols1.keySet());
        allColumns.addAll(cols2.keySet());

        for (String colName : allColumns) {
            ColumnInfo c1 = cols1.get(colName);
            ColumnInfo c2 = cols2.get(colName);

            // 如果某一个数据库缺少列，则标记为完全差异
            if (c1 == null) {
                if (c2 != null) {
                    c2.setHasDifference(true);
                    c2.setDifferenceType("missing_in_db1");
                }
                continue;
            }
            
            if (c2 == null) {
                if (c1 != null) {
                    c1.setHasDifference(true);
                    c1.setDifferenceType("missing_in_db2");
                }
                continue;
            }
            
            // 比较两个列的属性
            boolean isDifferent = false;
            
            // 比较数据类型
            if (!Objects.equals(c1.getDataType(), c2.getDataType())) {
                c1.setDataTypeDiff(true);
                c2.setDataTypeDiff(true);
                isDifferent = true;
            }
            
            // 比较列注释
            if (!equalsIgnoreNull(c1.getColumnComment(), c2.getColumnComment())) {
                c1.setColumnCommentDiff(true);
                c2.setColumnCommentDiff(true);
                isDifferent = true;
            }
            
            // 比较可空性
            if (!equalsIgnoreNull(c1.getIsNullable(), c2.getIsNullable())) {
                c1.setNullableDiff(true);
                c2.setNullableDiff(true);
                isDifferent = true;
            }
            
            // 比较键信息
            if (!equalsIgnoreNull(c1.getColumnKey(), c2.getColumnKey())) {
                c1.setColumnKeyDiff(true);
                c2.setColumnKeyDiff(true);
                isDifferent = true;
            }
            
            // 比较额外信息
            if (!equalsIgnoreNull(c1.getExtra(), c2.getExtra())) {
                c1.setExtraDiff(true);
                c2.setExtraDiff(true);
                isDifferent = true;
            }
            
            // 比较默认值
            if (!equalsIgnoreNull(c1.getDefaultValue(), c2.getDefaultValue())) {
                c1.setDefaultValueDiff(true);
                c2.setDefaultValueDiff(true);
                isDifferent = true;
            }
            
            // 如果有任何差异，标记整列有差异
            if (isDifferent) {
                c1.setHasDifference(true);
                c1.setDifferenceType("attribute_diff");
                c2.setHasDifference(true);
                c2.setDifferenceType("attribute_diff");
            }
        }
    }
    
    /**
     * 标记差异的索引
     */
    public static void markIndexDifferences(TableInfo info1, TableInfo info2) {
        Map<String, IndexInfo> indexes1 = new HashMap<>();
        Map<String, IndexInfo> indexes2 = new HashMap<>();

        if (info1.getIndexes() != null) {
            for (IndexInfo index : info1.getIndexes()) {
                indexes1.put(index.getIndexName(), index);
            }
        }
        
        if (info2.getIndexes() != null) {
            for (IndexInfo index : info2.getIndexes()) {
                indexes2.put(index.getIndexName(), index);
            }
        }

        Set<String> allIndexes = new HashSet<>();
        allIndexes.addAll(indexes1.keySet());
        allIndexes.addAll(indexes2.keySet());

        for (String indexName : allIndexes) {
            IndexInfo i1 = indexes1.get(indexName);
            IndexInfo i2 = indexes2.get(indexName);

            // 如果某一个数据库缺少索引，则标记为完全差异
            if (i1 == null) {
                if (i2 != null) {
                    i2.setHasDifference(true);
                    i2.setDifferenceType("missing_in_db1");
                }
                continue;
            }
            
            if (i2 == null) {
                if (i1 != null) {
                    i1.setHasDifference(true);
                    i1.setDifferenceType("missing_in_db2");
                }
                continue;
            }
            
            // 比较两个索引的属性
            boolean isDifferent = false;
            
            // 比较索引类型
            if (!Objects.equals(i1.getIndexType(), i2.getIndexType())) {
                isDifferent = true;
            }
            
            // 比较索引方法
            if (!Objects.equals(i1.getIndexMethod(), i2.getIndexMethod())) {
                isDifferent = true;
            }
            
            // 比较列名
            if (!Objects.equals(i1.getColumnNames(), i2.getColumnNames())) {
                isDifferent = true;
            }
            
            // 比较列排序
            if (!Objects.equals(i1.getColumnOrders(), i2.getColumnOrders())) {
                isDifferent = true;
            }
            
            // 比较non_unique属性
            if (i1.isNonUnique() != i2.isNonUnique()) {
                isDifferent = true;
            }
            
            // 如果有任何差异，标记整个索引有差异
            if (isDifferent) {
                i1.setHasDifference(true);
                i1.setDifferenceType("attribute_diff");
                i2.setHasDifference(true);
                i2.setDifferenceType("attribute_diff");
            }
        }
    }
    
    /**
     * 判断两个列是否相等
     */
    public static boolean isColumnEqual(ColumnInfo c1, ColumnInfo c2) {
        return Objects.equals(c1.getDataType().toLowerCase(), c2.getDataType().toLowerCase()) &&
               equalsIgnoreNull(c1.getColumnComment(), c2.getColumnComment()) &&
               equalsIgnoreNull(c1.getIsNullable(), c2.getIsNullable()) &&
               equalsIgnoreNull(c1.getColumnKey(), c2.getColumnKey()) &&
               equalsIgnoreNull(c1.getExtra(), c2.getExtra()) &&
               equalsIgnoreNull(c1.getDefaultValue(), c2.getDefaultValue());
    }
    
    /**
     * 判断两个索引是否相等
     */
    public static boolean isIndexEqual(IndexInfo i1, IndexInfo i2) {
        return Objects.equals(i1.getIndexType(), i2.getIndexType()) &&
               Objects.equals(i1.getIndexMethod(), i2.getIndexMethod()) &&
               Objects.equals(i1.getColumnNames(), i2.getColumnNames()) &&
               Objects.equals(i1.getColumnOrders(), i2.getColumnOrders()) &&
               i1.isNonUnique() == i2.isNonUnique();
    }
    
    /**
     * 生成列定义
     */
    public static String generateColumnDefinition(ColumnInfo column) {
        StringBuilder sb = new StringBuilder();
        sb.append("`").append(column.getColumnName()).append("` ");
        sb.append(column.getDataType()); // 使用完整数据类型（包含长度信息）
        
        // 处理可空性
        if ("NO".equalsIgnoreCase(column.getIsNullable()) || "false".equalsIgnoreCase(column.getIsNullable())) {
            sb.append(" NOT NULL");
        } else {
            sb.append(" NULL");
        }
        
        // 处理默认值
        if (column.getDefaultValue() != null) {
            if ("NULL".equalsIgnoreCase(column.getDefaultValue())) {
                sb.append(" DEFAULT NULL");
            } else {
                sb.append(" DEFAULT '").append(column.getDefaultValue()).append("'");
            }
        }
        
        // 处理额外信息（如自增）
        if (column.getExtra() != null && !column.getExtra().isEmpty()) {
            sb.append(" ").append(column.getExtra().toUpperCase());
        }
        
        // 处理注释
        if (column.getColumnComment() != null && !column.getColumnComment().isEmpty()) {
            sb.append(" COMMENT '").append(column.getColumnComment()).append("'");
        }
        
        return sb.toString();
    }
    
    /**
     * 生成索引定义
     */
    public static String generateIndexDefinition(IndexInfo index) {
        StringBuilder sb = new StringBuilder();
        
        switch (index.getIndexType().toUpperCase()) {
            case "PRIMARY":
                sb.append("PRIMARY KEY (");
                break;
            case "UNIQUE":
                sb.append("UNIQUE KEY `").append(index.getIndexName()).append("` (");
                break;
            case "FULLTEXT":
                sb.append("FULLTEXT KEY `").append(index.getIndexName()).append("` (");
                break;
            default:
                sb.append("KEY `").append(index.getIndexName()).append("` (");
                break;
        }
        
        List<String> columnNames = index.getColumnNames();
        List<String> columnOrders = index.getColumnOrders();
        
        for (int i = 0; i < columnNames.size(); i++) {
            if (i > 0) {
                sb.append(", ");
            }
            sb.append("`").append(columnNames.get(i)).append("`");
            
            // 添加排序信息（如果不是默认的ASC）
            if (i < columnOrders.size() && "DESC".equalsIgnoreCase(columnOrders.get(i))) {
                sb.append(" DESC");
            }
        }
        
        sb.append(")");
        
        // 添加索引方法（如BTREE, HASH等）
        if (index.getIndexMethod() != null && !index.getIndexMethod().isEmpty() && !"PRIMARY".equals(index.getIndexType())) {
            sb.append(" USING ").append(index.getIndexMethod());
        }
        
        return sb.toString();
    }
    
    /**
     * 生成创建表的SQL语句
     */
    public static List<String> generateCreateTableSqlStatements(TableInfo masterInfo, String tableName) {
        List<String> sqlStatements = new ArrayList<>();
        
        sqlStatements.add("-- 创建表 " + tableName + " 以匹配主库结构");
        
        StringBuilder createSql = new StringBuilder();
        createSql.append("CREATE TABLE `").append(tableName).append("` (\n");
        
        // 添加所有列定义
        List<ColumnInfo> columns = masterInfo.getColumns();
        for (int i = 0; i < columns.size(); i++) {
            ColumnInfo column = columns.get(i);
            createSql.append("  ").append(generateColumnDefinition(column));
            // 如果不是最后一列，或者有索引需要添加，则添加逗号
            if (i < columns.size() - 1 || (masterInfo.getIndexes() != null && !masterInfo.getIndexes().isEmpty())) {
                createSql.append(",");
            }
            createSql.append("\n");
        }
        
        // 添加索引定义
        if (masterInfo.getIndexes() != null && !masterInfo.getIndexes().isEmpty()) {
            List<IndexInfo> indexes = masterInfo.getIndexes();
            for (int i = 0; i < indexes.size(); i++) {
                IndexInfo index = indexes.get(i);
                createSql.append("  ").append(generateIndexDefinition(index));
                // 如果不是最后一个索引，则添加逗号
                if (i < indexes.size() - 1) {
                    createSql.append(",");
                }
                createSql.append("\n");
            }
        }
        
        // 添加表注释
        if (masterInfo.getTableComment() != null && !masterInfo.getTableComment().isEmpty()) {
            createSql.append(") COMMENT='").append(masterInfo.getTableComment()).append("';");
        } else {
            createSql.append(");");
        }
        
        sqlStatements.add(createSql.toString());
        return sqlStatements;
    }
    
    /**
     * 生成ALTER SQL语句，将数据库2修改为与数据库1一致
     */
    public static List<String> generateAlterSqlStatements(TableInfo info1, TableInfo info2, String tableName, String dbUrl2) {
        List<String> sqlStatements = new ArrayList<>();
        
        // 添加表名和基础语句
        sqlStatements.add("-- 修改表 " + tableName + " 以匹配目标结构");
        
        // 处理表注释
        if (!Objects.equals(info1.getTableComment(), info2.getTableComment())) {
            String comment = info1.getTableComment() != null ? info1.getTableComment() : "";
            sqlStatements.add(String.format("ALTER TABLE `%s` COMMENT='%s';", tableName, comment));
        }
        
        Map<String, ColumnInfo> cols1 = new HashMap<>();
        Map<String, ColumnInfo> cols2 = new HashMap<>();

        for (ColumnInfo col : info1.getColumns()) {
            cols1.put(col.getColumnName(), col);
        }
        for (ColumnInfo col : info2.getColumns()) {
            cols2.put(col.getColumnName(), col);
        }

        Set<String> allColumns = new HashSet<>();
        allColumns.addAll(cols1.keySet());
        allColumns.addAll(cols2.keySet());

        List<String> addColumnStatements = new ArrayList<>();
        List<String> dropColumnStatements = new ArrayList<>();
        List<String> modifyColumnStatements = new ArrayList<>();

        for (String colName : allColumns) {
            ColumnInfo c1 = cols1.get(colName);
            ColumnInfo c2 = cols2.get(colName);

            // 如果数据库2中缺少列，则添加列
            if (c1 != null && c2 == null) {
                addColumnStatements.add(String.format("ALTER TABLE `%s` ADD COLUMN %s;", tableName, generateColumnDefinition(c1)));
            } 
            // 如果数据库1中缺少列，而数据库2中有，则删除列
            else if (c1 == null && c2 != null) {
                dropColumnStatements.add(String.format("ALTER TABLE `%s` DROP COLUMN `%s`;", tableName, colName));
            } 
            // 如果两个数据库都有该列，但属性不同，则修改列
            else if (c1 != null && c2 != null) {
                if (!isColumnEqual(c1, c2)) {
                    modifyColumnStatements.add(String.format("ALTER TABLE `%s` MODIFY COLUMN %s;", tableName, generateColumnDefinition(c1)));
                }
            }
        }
        
        // 处理索引差异
        Map<String, IndexInfo> indexes1 = new HashMap<>();
        Map<String, IndexInfo> indexes2 = new HashMap<>();
        
        if (info1.getIndexes() != null) {
            for (IndexInfo index : info1.getIndexes()) {
                indexes1.put(index.getIndexName(), index);
            }
        }
        
        if (info2.getIndexes() != null) {
            for (IndexInfo index : info2.getIndexes()) {
                indexes2.put(index.getIndexName(), index);
            }
        }
        
        Set<String> allIndexes = new HashSet<>();
        allIndexes.addAll(indexes1.keySet());
        allIndexes.addAll(indexes2.keySet());
        
        List<String> addIndexStatements = new ArrayList<>();
        List<String> dropIndexStatements = new ArrayList<>();
        
        for (String indexName : allIndexes) {
            IndexInfo i1 = indexes1.get(indexName);
            IndexInfo i2 = indexes2.get(indexName);
            
            // 如果数据库2中缺少索引，则添加索引
            if (i1 != null && i2 == null) {
                addIndexStatements.add(String.format("ALTER TABLE `%s` ADD %s;", tableName, generateIndexDefinition(i1)));
            }
            // 如果数据库1中缺少索引，而数据库2中有，则删除索引
            else if (i1 == null && i2 != null) {
                if (!"PRIMARY".equals(indexName)) { // 主键索引需要特殊处理
                    dropIndexStatements.add(String.format("ALTER TABLE `%s` DROP INDEX `%s`;", tableName, indexName));
                }
            }
            // 如果两个数据库都有该索引，但属性不同，则先删除再添加
            else if (i1 != null && i2 != null) {
                if (!isIndexEqual(i1, i2)) {
                    if (!"PRIMARY".equals(indexName)) { // 主键索引需要特殊处理
                        dropIndexStatements.add(String.format("ALTER TABLE `%s` DROP INDEX `%s`;", tableName, indexName));
                    }
                    addIndexStatements.add(String.format("ALTER TABLE `%s` ADD %s;", tableName, generateIndexDefinition(i1)));
                }
            }
        }
        
        // 按照标准顺序添加语句：删除索引 -> 删除列 -> 修改列 -> 添加列 -> 添加索引
        sqlStatements.addAll(dropIndexStatements);
        sqlStatements.addAll(dropColumnStatements);
        sqlStatements.addAll(modifyColumnStatements);
        sqlStatements.addAll(addColumnStatements);
        sqlStatements.addAll(addIndexStatements);
        
        return sqlStatements;
    }
    
    /**
     * 比较两个值是否相等，其中null和"NULL"视为相等
     */
    private static boolean equalsIgnoreNull(String s1, String s2) {
        if (s1 == null && s2 == null) {
            return true;
        }
        if (s1 == null) {
            return "NULL".equalsIgnoreCase(s2) || "".equals(s2);
        }
        if (s2 == null) {
            return "NULL".equalsIgnoreCase(s1) || "".equals(s1);
        }
        return s1.equalsIgnoreCase(s2);
    }
    
    /**
     * 用于显示的值，将null显示为"NULL"
     */
    private static String getValueForDisplay(String value) {
        return value == null ? "NULL" : value;
    }
}
